#!/usr/bin/perl 
use strict;
use Getopt::Long;
use DBI;
################################################################################
## Kontrollkit for MySQL
## NAME: mt-daily-optimize.pl
## DATE: 2009-05-07
## AUTHOR: Matt Reid
## WEBSITE: http://kontrollsoft.com
## EMAIL: kontact@kontrollsoft.com
## LICENSE: BSD http://www.opensource.org/licenses/bsd-license.php
################################################################################
## Copyright 2008 Matt Reid
## All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
# 1. Redistributions of source code must retain the above copyright
#    notice, this list of conditions and the following disclaimer.
# 2. Redistributions in binary form must reproduce the above copyright
#    notice, this list of conditions and the following disclaimer in the
#    documentation and/or other materials provided with the distribution.
# 3. The name of the author may not be used to endorse or promote products
#    derived from this software without specific prior written permission.
#
# THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR
# IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
# OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
# IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT,
# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT
# NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
# DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
# THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
# THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
##################################################################################
#
## WHAT THIS SCRIPT DOES #########################################################
#This script optimized mysql on a daily basis
###########################################
#Set your options here please
###########################################
my $opt = {
    'host' => 'localhost',
    'user' => 'root',
    'pass' => 'password'
    };

## If you only want to run this script on certain tables, please specify them here
## as such: "schema.table" and separate them with a comma if operating on more
## than one table. ex: my @tables = ("schema0:foo","schema1:bar");
my @tables = ();
    

## END OF USER SETTINGS
###########################################
#
GetOptions($opt, "host=s", "c=i", "w=i", "user=s", "pass=s", "help");

sub flush {
    my $HOST = $opt>{'host'};
    my $USER = $opt->{'user'};
    my $PASS = $opt->{'pass'};

    my $dbh = DBI->connect("DBI:mysql:host=$HOST",$USER,$PASS) or die "CRIT: cannot connect to $HOST. Exiting.";
    if (not $dbh) {
        print "CRIT: cannot connect to $HOST";
        exit 1;
    }

    if(@tables) {
	for my $t (@tables) {
	    print "ANALYZING TABLE: $t\n";
	    $dbh->do("analyze table $t;");
	    print "OPTIMIZING TABLE: $t\n";
	    $dbh->do("optimize table $t;");
	}
    }
    else {
	my @dbList = $dbh->func('_ListDBs');
	for my $schema (@dbList) {
	    if($schema eq 'information_schema'){
		print "\nNot operating on information_schema\n";
	    }
	    else {
		print "\nOperating on Schema: $schema\n";
		$dbh->do("use $schema;");
		my $sql = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '$schema';";
		my $sth = $dbh->prepare($sql) or die "mysql connect failed";
		$sth->execute or die "executing: ", $dbh->errstr;
		while (my $row = $sth->fetchrow_hashref) {
		    my $table = $row->{'TABLE_NAME'};
		    print "ANALYZING TABLE: $schema.$table\n";
		    $dbh->do("analyze table $schema.$table;");
		    print "OPTIMIZING TABLE: $schema.$table\n";
		    $dbh->do("optimize table $schema.$table;");
		}
	    }
	}
    }    
    print "FLUSHING QUERY CACHE\n";
    $dbh->do("FLUSH QUERY CACHE;");
    $dbh->disconnect();
}

print<<HELP;
--------------------------
mt-daily-optimize
Kontrollkit                    
http://kontrollsoft.com
2009-05-07                               
This script analyzes and optimized every table.
--------------------------
    Script will run in 5 seconds...
HELP

system("sleep 5");
flush();
